import threading

import pymysql
from other.cfg import Config
from server.synchronization import Synchronization
from loguru import logger


class MySQL:
    def __init__(self, database, tablename):
        self.cfg = Config()
        self.db = None
        self.cursor = None
        self.database = database
        self.tablename = tablename
        self.connect()

    def connect(self):
        try:
            self.db = pymysql.connect(host=self.cfg.res['mysql']['host'], user=self.cfg.res['mysql']['user'],
                                      password=self.cfg.res['mysql']['password'], database=f"{self.database}",
                                      cursorclass=pymysql.cursors.DictCursor)
            # 创建游标对象
            self.cursor = self.db.cursor()
            print("连接成功!!！")
        except:
            print("连接失败！")

    def construction(self, table_name):
        # 执行 SQL 查询获取表字段信息
        sql = f"DESCRIBE {table_name}"
        self.cursor.execute(sql)
        # 获取查询结果
        result = self.cursor.fetchall()
        print('result',result)
        # 构造 field_types 字典
        field_types = {}
        for row in result:
            field_name = row['Field']  # 使用字段名称访问
            field_type = row['Type']  # 使用字段类型访问

            # 根据字段类型映射到相应的 Python 类型
            if 'int' in field_type or 'bigint' in field_type:
                field_types[field_name] = 'int'
            elif 'float' in field_type:
                field_types[field_name] = 'float'
            elif 'varchar' in field_type or 'text' in field_type or 'char' in field_type:
                field_types[field_name] = 'str'
            elif 'datetime' in field_type:
                field_types[field_name] = 'datetime'
            # 可以根据需要添加更多字段类型映射
        # print(field_types)
        return field_types

    # 查询
    def get(self, data):
        try:
            sql = "select * from `{}` {}".format(self.tablename, data)
            print('mysql_get', sql)
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
        except Exception as e:
            self.connect()
            sql = "select * from `{}` {}".format(self.tablename, data)
            print('mysql_get', sql)
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
        return res

    def other_add(self, sql, data):
        try:
            print('mysql_add', sql)
            self.cursor.execute(sql, data)
            id = self.cursor.lastrowid
            # print('idddd', id)
            self.db.commit()
        except Exception as e:
            self.connect()
            sql = "insert into `{}` {}".format(self.tablename, data)
            print('mysql_add', sql)
            self.cursor.execute(sql)
            id = self.cursor.lastrowid
            print('idddd', id)
            self.db.commit()
        return id

    # 增加
    def add(self, data):
        try:
            sql = "insert into `{}` {}".format(self.tablename, data)
            print('mysql_add', sql)
            self.cursor.execute(sql)
            id = self.cursor.lastrowid
            # print('idddd', id)
            self.db.commit()
        except Exception as e:
            self.connect()
            sql = "insert into `{}` {}".format(self.tablename, data)
            print('mysql_add', sql)
            self.cursor.execute(sql)
            id = self.cursor.lastrowid
            print('idddd', id)
            self.db.commit()
        return id

    # 修改
    def edit(self, data):
        try:
            sql = "update `{}` set {}".format(self.tablename, data)
            print('mysql_edit', sql)
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            self.connect()
            sql = "update `{}` set {}".format(self.tablename, data)
            print('mysql_edit', sql)
            self.cursor.execute(sql)
            self.db.commit()

    # 删除
    def delete(self, data):
        try:
            sql = "delete from `{}` {}".format(self.tablename, data)
            print('mysql_delete', sql)
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            self.connect()
            sql = "delete from `{}` {}".format(self.tablename, data)
            print('mysql_delete', sql)
            self.cursor.execute(sql)
            self.db.commit()

    def gets(self, data):
        try:
            sql = data
            print('mysql_gets', sql)
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            return res
        except Exception as e:
            self.connect()
            sql = data
            print('mysql_gets', sql)
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            return res

    # 增加
    def adds(self, data, table_name):
        try:
            sql = data
            print('mysql_adds', sql)
            self.cursor.execute(sql)
            id = self.cursor.lastrowid
            # print('idddd', id)
            self.db.commit()
        except Exception as e:
            self.connect()
            sql = data
            print('mysql_adds', sql)
            self.cursor.execute(sql)
            id = self.cursor.lastrowid
            print('idddd', id)
            self.db.commit()
        return id

    def edits(self, data, table_name):
        try:
            sql = data
            print('mysql_edits', sql)
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            self.connect()
            sql = data
            print('mysql_edits', sql)
            self.cursor.execute(sql)
            self.db.commit()

    def Rereplace(self, sql, value):
        try:
            self.cursor.executemany(sql, value)
            self.db.commit()
        except Exception as e:
            self.connect()
            self.cursor.executemany(sql, value)
            self.db.commit()

    # 回滚提交
    def rollbacks(self, sqls):
        try:
            try:
                for sql in sqls:
                    # 执行一系列数据库操作
                    # 比如插入数据、更新数据等
                    self.cursor.execute(sql)
                    # 如果没有出错，提交事务
                    self.db.commit()
                    logger.info(f"数据提交成功:{sql}")
            except Exception as e:
                # 出现异常时回滚事务
                self.db.rollback()
                print("Transaction rolled back:", e)
                logger.info(f"数据提交失败:{e}")
        except Exception as e:
            self.connect()
            try:
                for sql in sqls:
                    # 执行一系列数据库操作
                    # 比如插入数据、更新数据等
                    self.cursor.execute(sql)
                    # 如果没有出错，提交事务
                    self.db.commit()
                    logger.info(f"数据提交成功:{sql}")
            except Exception as e:
                # 出现异常时回滚事务
                self.db.rollback()
                print("Transaction rolled back:", e)
                logger.info(f"数据提交失败:{e}")

    def __del__(self):
        self.cursor.close()
        self.db.close()
